package com.dao.impl;

import com.dao.FoodsDao;
import com.entity.Cart;
import com.entity.Foods;
import com.entity.Like;
import com.entity.Orders;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class FoodsDaoImpl implements FoodsDao {

    private QueryRunner runner=new QueryRunner();

    /**
     * 查询所有食物
     */
    @Override
    public List<Foods> foodsFindAll() {
        Connection connection = DruidUtils.getConnection();
        List<Foods> foods=null;
        String sql = "select * from foods;";
        try {
            foods = runner.query(connection,sql,new BeanListHandler<>(Foods.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 查询所有食物存进map集合
     * @return
     */
    @Override
    public List<Map<String, Object>> foodFindAll() {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID GROUP BY food_id ";
        try {
            foods = runner.query(connection,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }


    /**
     * 查询点赞数量
     * @param
     * @return
     */
    public  List<Like> findLike(){
        Connection connection = DruidUtils.getConnection();
        String sql = "select id,SUM(`like`) `like` ,food_id  from `like`  GROUP BY food_id;";
        List<Like> like=null;
        try {
            like = runner.query(connection,sql,new BeanListHandler<>(Like.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return like;
    }


    /**
     *查询三个最受欢迎的食物信息
     * @return
     */
    @Override
    public List<Map<String, Object>> findthree() {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID GROUP BY food_id ORDER BY `like` DESC LIMIT 3;";
        List<Map<String, Object>> like=null;
        try {
            like = runner.query(connection,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return like;
    }

    /**
     * 查询用户是否点赞
     * @return
     */
    public  List<Like> findUserLove(){
        Connection connection = DruidUtils.getConnection();
        String sql = "select *  from `like`;";
        List<Like> Love=null;
        try {
            Love = runner.query(connection,sql,new BeanListHandler<>(Like.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return Love;
    }

    /**
     * 分页查询菜单列表
     * @param pageIndex
     * @param
     * @return
     */
    @Override
    public List<Foods> foodList(int pageIndex,int pageSize) {
        Connection connection = DruidUtils.getConnection();
        String sql = "select * from foods limit ?,?";
        int offset=(pageIndex-1)*pageSize;
        try {
            List<Foods> query = runner.query(connection, sql, new BeanListHandler<>(Foods.class), offset, pageSize);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return null;
    }

    /**
     * 分页查询
     * @return
     */
    @Override
    public int getCount(){
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from foods";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>());
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 点赞
     * @param customerId
     * @param foodId
     * @return
     */
    @Override
    public int foodGiveLike(int customerId, int foodId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "insert into `like` (bigdata.`like`.`like`, bigdata.`like`.customer_id, bigdata.`like`.food_id) values (?,?,?)";
        try {
            int num=runner.update(connection,sql,1,customerId,foodId);
            return num;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 取消点赞
     * @param customerId1
     * @param foodId1
     * @return
     */
    @Override
    public int foodCancelLike(int customerId1, int foodId1) {
        Connection connection = DruidUtils.getConnection();
        String sql = "delete from `like` where bigdata.`like`.customer_id=? and bigdata.`like`.food_id=?";
        try {
            int num=runner.update(connection,sql,customerId1,foodId1);
            return num;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 寻找这个食物的赞
     * @param customerId
     * @param foodId
     * @return
     */
    @Override
    public List<Like> findFoodLike(int customerId, int foodId) {
        Connection connection = DruidUtils.getConnection();
        List<Like> foods=null;
        String sql = "select * from bigdata.`like` where customer_id=? AND food_id=?;";
        try {
            foods = runner.query(connection,sql,new BeanListHandler<>(Like.class),customerId,foodId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 按照id查询食物信息
     * @return
     */
    @Override
    public List<Map<String, Object>> findId(int foodId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT * FROM foods WHERE ID=?;";
        List<Map<String, Object>> foods=null;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),foodId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 按照id查询点赞数量
     * @param likeId
     * @return
     */
    @Override
    public List<Map<String, Object>> likeId(int likeId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT SUM(`like`) `like` FROM `like` WHERE food_id=? GROUP BY food_id;";
        List<Map<String, Object>> foods=null;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),likeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 根据食物类型查询数据
     * @param typeId
     * @return
     */
    @Override
    public List<Map<String, Object>> typeId(int typeId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID and type_id=? GROUP BY food_id ;";
        List<Map<String, Object>> foods=null;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 添加购物车
     * @param userId
     * @return
     */
    @Override
    public int updataCart(int price,int number,int userId,int foodId,int size,String fall) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="INSERT INTO `cart`(price,number,customers_id,food_id,size,fall) VALUES(?,?,?,?,?,?)";
            int update = runner.update(connection, sql,price,number,userId,foodId,size,fall);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 查重
     * @param userId
     * @return
     */
    @Override
    public List<Map<String, Object>> listCart(int price,int number,int userId,int foodId,int size) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="select * from `cart` WHERE price=? AND size=? AND customers_id=? AND food_id=? ";
            List<Map<String, Object>> update = runner.query(connection, sql,new MapListHandler(),price,size,userId,foodId);
            if (update.size()==0){
                return null;
            }else {
                String sql1="UPDATE cart SET number=? WHERE customers_id=? and food_id=?;";
                 int query = runner.update(connection, sql1, number, userId, foodId);
                 return update;

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return null;
    }

    /**
     * 根据id改变购物车数量
     * @param listCartId
     * @return
     */
    @Override
    public int updetaNumber(int listCartId,int number) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="UPDATE cart SET number=? WHERE id=?";
            int update = runner.update(connection, sql,number,listCartId);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 删除购物车数据
     * @param listCartId
     * @return
     */
    @Override
    public int removeCart(int listCartId) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="delete FROM cart  WHERE id=?";
            int update = runner.update(connection, sql,listCartId);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    /**
     * 根据用户id查找数据购物车
     * @param userId
     * @return
     */
    @Override
    public List<Cart> findCart(int userId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT c.id,c.price,c.fall,c.customers_id,c.food_id,c.number,c.size,f.photo,f.`name` FROM cart c,foods f WHERE c.customers_id=? AND f.ID=c.food_id";
        List<Cart> foods=null;
        try {
            foods = runner.query(connection,sql,new BeanListHandler<>(Cart.class),userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 更具id查询食物id
     * @param id
     * @return
     */
    @Override
    public Foods getById(long id) {
        Connection connection = DruidUtils.getConnection();
        String sql = "select * from foods where id=?";
        try {
            Foods query = runner.query(connection, sql, new BeanHandler<>(Foods.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }


    /**
     * 删除
     * @param id
     * @return
     */
    @Override
    public int deleteFood(int id) {
        Connection connection = DruidUtils.getConnection();
        String sql="DELETE FROM foods WHERE id=?";
        try {
            int update = runner.update(connection, sql, id);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int modify(Foods foods) {
        Connection connection = DruidUtils.getConnection();
        String sql = "UPDATE foods set name=?,photo=?,price=?,type_id=?,`describe`=? WHERE id=?";
        try {
            int update = runner.update(connection, sql, foods.getName(), foods.getPhoto(), foods.getPrice(), foods.getType_id(), foods.getDescribe(), foods.getId());
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int add(String name, String photo, int price, String describe,int type_id) {
        Connection connection = DruidUtils.getConnection();
        String sql="INSERT into foods(name,photo,type_id,price,`describe`) VALUES(?,?,?,?,?)";
        try {
            int update = runner.update(connection, sql, name, photo, type_id, price, describe);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 查询用户订单信息
     * @param id
     * @return
     */
    @Override
    public Orders orderById(int id) {
        Connection connection = DruidUtils.getConnection();
        String sql="select o.id,o.customers_id,o.food_id,o.money,o.number,o.oNumber,o.state from orders as o,customers where customers.ID=customers_id and customers.ID=? and o.state=0";
        try {
            Orders query = runner.query(connection, sql, new BeanHandler<>(Orders.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 通过订单号查询信息
     * @param onNumber
     * @return
     */
    @Override
    public List<Orders> orderOnNum(long onNumber) {
        Connection connection = DruidUtils.getConnection();
        String sql="SELECT * from orders WHERE oNumber=?";
        try {
            List<Orders> query = runner.query(connection, sql, new BeanListHandler<>(Orders.class), onNumber);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 将购物车的数据添加到订单表
     * @param userId
     * @return
     */
    @Override
    public int addOrder(int userId) {
        Connection connection = DruidUtils.getConnection();
        //查询数据
        FoodsDao foodsDao=new FoodsDaoImpl();
        List<Cart> list = foodsDao.findCart(userId);
        SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMddhhmmss");
        String format = sdf.format(new Date());
        //存储数据到订单表
        for(Cart list1:list){
            String sql="INSERT INTO orders(number,money,customers_id,food_id,ordertime,oNumber) VALUES(?,?,?,?,NOW(),?)";
            try {
                int update = runner.update(connection, sql,list1.getNumber(),list1.getSubtotal(),list1.getCustomers_id(),list1.getFood_id(),format);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        //删除购物车
        String sql1="delete FROM cart  WHERE customers_id=?";
        try {
            int update = runner.update(connection, sql1,userId);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    public static void main(String[] args) {
        Date date = new Date();
        SimpleDateFormat dateFormat= new SimpleDateFormat("yyyyMMddhhmmss");
        System.out.println(dateFormat.format(date));
    }

    @Override
    public List<Map<String, Object>> bgFoodsFindAll(int pageIndex, int pageSize) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>>foods=null;
        String sql = "select f.ID,f.`name`,f.price,f.`describe`,f.business_id,t.`name` typename,t.ID typeId,f.photo from foods f inner join type t on f.type_id = t.ID order by ID desc limit ?,?;";
        int offset=(pageIndex-1)*pageSize;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),offset,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    @Override
    public List<Map<String, Object>> bgFoodGetByID(int foodId) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>>foods=null;
        String sql = "select f.ID,f.`name`,f.price,f.`describe`,f.business_id,t.`name` typename,t.ID typeId,f.photo from foods f inner join type t on f.type_id = t.ID where f.ID=?;";
        try {
            foods = runner.query(connection,sql,new MapListHandler(),foodId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    @Override
    public List<Map<String, Object>> bgFoodGetByName(String foodName) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>>foods=null;
        foodName="%"+foodName+"%";
        String sql = "select f.ID,f.`name`,f.price,f.`describe`,f.business_id,t.`name` typename,t.ID typeId,f.photo from foods f inner join type t on f.type_id = t.ID WHERE f.name LIKE ?;";
        try {
            foods = runner.query(connection,sql,new MapListHandler(),foodName);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    @Override
    public List<Map<String, Object>> getOrderByUserId(int userId) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>>order=null;
        String sql="select DISTINCT orders.oNumber,orders.ordertime,orders.state FROM orders WHERE orders.customers_id=?;";
        try {
            order = runner.query(connection,sql,new MapListHandler(),userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return order;
    }

//    public static void main(String[] args) {
//        FoodsDao foodsDao=new FoodsDaoImpl();
//        List<Map<String, Object>> foodLike = foodsDao.bgFoodGetByName("汉堡");
//        System.out.println(foodLike);
//    }
}
